﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Data.Models;
using AccessControl.Contracts.Models;
using System.Collections.Generic;

namespace AccessControl.DataManagers.Tables.Managers
{
    /// <summary>
    /// Manages the users in the DB 
    /// </summary>
    class UsersManager : TableManager<IUser>
    {
        #region Fields

        private static UsersManager s_Instance = null;
        private SqlDataAdapter _usersAdapter, _userEntriesAdapter;

		private static readonly string COL_1_ID = "ID";
		private static readonly string COL_2_NAME = "NAME";

		private static readonly string COL_1_ENTRY_ID = "ENTRY_ID";
		private static readonly string COL_2_USER_ID = "USER_ID";

        #endregion

        #region Private Constructor

        /// <summary>
        /// Prevents a default instance of the <see cref="UsersManager"/> class from being created.
        /// </summary>
        private UsersManager()
        {
        }

        #endregion

        #region Public Properties

        /// <summary>
        /// Gets the instance.
        /// </summary>
        public static UsersManager Instance
        {
            get
            {
                lock (typeof(UsersManager))
                {
                    if (s_Instance == null)
                        s_Instance = new UsersManager();

                    return s_Instance;
                }
            }
        }

        #endregion

        #region TableManager Overrides

        /// <summary>
        /// Creates using the specified connection.
        /// </summary>
        /// <param name="connection">The connection.</param>
        public override void Create(SqlConnection connection)
        {
            /* Create the users table */
            SqlCommand command = connection.CreateCommand();
            command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} TEXT)", 
												TablesNames.USERS, COL_1_ID, COL_2_NAME);
            command.ExecuteNonQuery();

            /* And the entries for users table */
            command = connection.CreateCommand();
            command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} INT)", 
												TablesNames.ENTRIES_FOR_USERS, COL_1_ENTRY_ID, COL_2_USER_ID);
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Initializes this instance.
        /// </summary>
        public override void Initialize(SqlConnection connection)
        {
			/* Create the adapters */
			_usersAdapter = new SqlDataAdapter();
			_userEntriesAdapter = new SqlDataAdapter();

			/* And fill the commands */
			_usersAdapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.USERS), connection);
			_usersAdapter.InsertCommand = new SqlCommand(string.Format("INSERT INTO {0} ({1}, {2}) VALUES (@{1}, @{2})",
														 TablesNames.USERS, COL_1_ID, COL_2_NAME), connection);
			_usersAdapter.UpdateCommand = new SqlCommand(string.Format("UPDATE {0} SET {2} = @{2} WHERE {1} = @{1}",
														TablesNames.USERS, COL_1_ID, COL_2_NAME), connection);
			_usersAdapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}", TablesNames.USERS, COL_1_ID), connection);

			_userEntriesAdapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.ENTRIES_FOR_USERS), connection);
			_userEntriesAdapter.InsertCommand = new SqlCommand(string.Format("INSERT INTO {0} ({1}, {2}) VALUES (@{1}, @{2})",
																TablesNames.ENTRIES_FOR_USERS, COL_1_ENTRY_ID, COL_2_USER_ID), connection);
			_userEntriesAdapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}",
																TablesNames.ENTRIES_FOR_USERS, COL_2_USER_ID), connection);
        }

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <returns></returns>
        public override bool FillList(IList<IUser> items)
        {
			User user;
			List<IEntry> entries = new List<IEntry>();

			/* Fill the entries list */
			EntriesManager.Instance.FillList(entries);

			/* First fill the users */
			using (SqlDataReader usersReader = _usersAdapter.SelectCommand.ExecuteReader())
			{
				/* Run on the users table */
				while (usersReader.Read())
				{
					user = new User()
					{
						Id = usersReader.GetInt32(0),
						Name = usersReader.GetString(1)
					};					

					/* And add the user */
					items.Add(user);
				}
			}

			/* Now fill the entries for that user */
			using (SqlDataReader entriesReader = _userEntriesAdapter.SelectCommand.ExecuteReader())
			{
				while (entriesReader.Read())
				{
					foreach (IUser userItem in items)
					{
						/* If the same user id */
						if (entriesReader.GetInt32(1) == userItem.Id)
						{
							/* Add the matching entry */
							foreach (IEntry entry in entries)
							{
								if (entry.Id == entriesReader.GetInt32(0))
								{
									userItem.AllowedEntries.Add(entry);
									break;
								}
							}
						}
					}
				}
			}

			return true;
        }

        /// <summary>
        /// Adds the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Add(IUser item)
        {
			/* First add the user */
			_usersAdapter.InsertCommand.Parameters.Clear();

			/* Set the item id */
			item.Id = GetHighestId(_usersAdapter.SelectCommand.Connection, TablesNames.USERS, COL_1_ID) + 1;

			_usersAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_usersAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_NAME, item.Name));
			_usersAdapter.InsertCommand.ExecuteNonQuery();

			/* Now add the entries */
			foreach (IEntry entry in item.AllowedEntries)
			{
				_userEntriesAdapter.InsertCommand.Parameters.Clear();
				_userEntriesAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_ENTRY_ID, entry.Id));
				_userEntriesAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_USER_ID, item.Id));
				_userEntriesAdapter.InsertCommand.ExecuteNonQuery();
			}

			return true;
        }

        /// <summary>
        /// Updates the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Update(IUser item)
        {
			/* First update the user */
			_usersAdapter.UpdateCommand.Parameters.Clear();
			_usersAdapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_usersAdapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_2_NAME, item.Name));
			_usersAdapter.UpdateCommand.ExecuteNonQuery();

			/* Now delete all the allowed entries for that user */
			_userEntriesAdapter.DeleteCommand.Parameters.Clear();
			_userEntriesAdapter.DeleteCommand.Parameters.Add(COL_2_USER_ID, item.Id);
			_userEntriesAdapter.DeleteCommand.ExecuteNonQuery();

			/* And add all the entries */
			foreach (IEntry allowedEntry in item.AllowedEntries)
			{
				_userEntriesAdapter.InsertCommand.Parameters.Clear();
				_userEntriesAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_ENTRY_ID, allowedEntry.Id));
				_userEntriesAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_USER_ID, item.Id));
				_userEntriesAdapter.InsertCommand.ExecuteNonQuery();
			}

			return true;

        }

        /// <summary>
        /// Deletes the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Delete(IUser item)
        {
			_usersAdapter.DeleteCommand.Parameters.Clear();
			_usersAdapter.DeleteCommand.Parameters.Add(COL_1_ID, item.Id);
			_usersAdapter.DeleteCommand.ExecuteNonQuery();

			_userEntriesAdapter.DeleteCommand.Parameters.Clear();
			_userEntriesAdapter.DeleteCommand.Parameters.Add(COL_2_USER_ID, item.Id);
			_userEntriesAdapter.DeleteCommand.ExecuteNonQuery();

			return true;
        }

        #endregion
    }
}
